import os
import django
from django.db import models
from django.contrib.auth.models import AbstractUser
from django.contrib.auth.models import User
from django.db.models.signals import pre_save
from django.dispatch import receiver
from django.utils import timezone

class ProductAnalysis(models.Model):
    """产品分析结果表"""
    productid = models.CharField(max_length=50, db_index=True)
    market_demand = models.FloatField(null=True, blank=True, verbose_name='市场需求指数')
    competition_level = models.FloatField(null=True, blank=True, verbose_name='竞争程度指数')
    profitability = models.FloatField(null=True, blank=True, verbose_name='盈利能力指数')
    growth_potential = models.FloatField(null=True, blank=True, verbose_name='增长潜力指数')
    surge_index = models.FloatField(null=True, blank=True, verbose_name='飙升指数')
    rank_change = models.FloatField(null=True, blank=True, verbose_name='排名变化')
    sales_change = models.FloatField(null=True, blank=True, verbose_name='销量变化')
    review_change = models.FloatField(null=True, blank=True, verbose_name='评论变化')
    is_surging = models.BooleanField(default=False, verbose_name='是否飙升')
    optimized_title = models.CharField(max_length=500, null=True, blank=True, verbose_name='优化后的标题')
    optimized_bullets = models.TextField(null=True, blank=True, verbose_name='优化后的五点描述(JSON格式)')
    keyword_suggestions = models.TextField(null=True, blank=True, verbose_name='关键词建议(JSON格式)')
    confidence_score = models.FloatField(null=True, blank=True, verbose_name='置信度分数')
    analysis_date = models.DateTimeField(default=timezone.now, verbose_name='分析日期')
    created_at = models.DateTimeField(auto_now_add=True, verbose_name='创建时间')
    updated_at = models.DateTimeField(auto_now=True, verbose_name='更新时间')

    class Meta:
        verbose_name = '产品分析'
        verbose_name_plural = '产品分析'
        ordering = ['-analysis_date']
        db_table = 'app_product_analysis'

    def __str__(self):
        return f'产品分析 - {self.productid}'

    @property
    def product(self):
        """关联产品"""
        from .models import ProductXue
        return ProductXue.objects.filter(productid=self.productid).first()

    @classmethod
    def get_latest_analysis(cls, productid):
        """获取产品最新的分析结果"""
        return cls.objects.filter(productid=productid).order_by('-analysis_date').first()

    @classmethod
    def get_surging_products(cls, limit=10):
        """获取飙升产品列表"""
        return cls.objects.filter(is_surging=True).order_by('-surge_index')[:limit]

    @classmethod
    def get_market_metrics(cls):
        """获取市场指标"""
        from django.db.models import Avg
        metrics = cls.objects.aggregate(
            avg_market_demand=Avg('market_demand'),
            avg_competition_level=Avg('competition_level'),
            avg_profitability=Avg('profitability')
        )
        
        return {
            'market_demand': metrics['avg_market_demand'] or 0,
            'competition_level': metrics['avg_competition_level'] or 0,
            'profitability': metrics['avg_profitability'] or 0
        }

class asinsupload_xue(models.Model):
    asin = models.CharField('ASIN', max_length=64, null=False)
    id=models.AutoField('ASIN编号',primary_key=True)
    opdate = models.DateTimeField('上传时间', auto_now=True)
    epid = models.CharField('企业编号', max_length=64, null=False)
    processdate=models.DateTimeField('抓取时间', null=True, blank=True)
    detail1=models.CharField('5点描述1', max_length=2000, null=True, blank=True)
    detail2=models.CharField('5点描述2', max_length=2000, null=True, blank=True)
    detail3=models.CharField('5点描述3', max_length=2000, null=True, blank=True)
    detail4=models.CharField('5点描述4', max_length=2000, null=True, blank=True)
    detail5=models.CharField('5点描述5', max_length=2000, null=True, blank=True)
    image_url=models.CharField('图片链接', max_length=2000, null=True, blank=True)
    
    class Meta:
        db_table = 'app_asinsupload_xue'
        verbose_name = "ASIN上传"
        verbose_name_plural = "ASIN上传"
class asinsmonitor_xue(models.Model):
    id = models.AutoField('ASIN编号',  primary_key=True)
    asin = models.CharField('ASIN', max_length=64, null=False)
    hoursdiff = models.IntegerField('监控频率：小时', null=False)
    opdate = models.DateTimeField('更新时间', auto_now=True)
    epid = models.CharField('企业编号', max_length=64, null=False)
    processdate=models.DateTimeField('处理时间', null=True, blank=True)

    class Meta:
        db_table = 'app_asinsmonitor_xue'
        verbose_name = "ASIN监控"
class CategoryXue(models.Model):
    id = models.AutoField('分类编号', primary_key=True)
    category = models.CharField('分类', max_length=64, null=False)
    parent_category = models.CharField('父分类', max_length=64, null=False)
    leaf_category = models.BooleanField('是否为叶子分类', default=False)
    parent_level = models.CharField('父分类级别', max_length=64, null=False)
    level = models.CharField('分类级别', max_length=64, null=False)
    url = models.CharField('分类链接', max_length=64, null=False)
    image_url = models.CharField('图片链接', max_length=64, null=False)
    alt_text = models.CharField('alt文本', max_length=64, null=False)
    currentpage = models.CharField('当前页', max_length=64, null=False)
    opdate = models.DateTimeField('更新时间', auto_now=True)
    need_download = models.BooleanField('是否需要下载', default=False)
    class Meta:
        db_table = 'app_category_xue'
        verbose_name = "亚马逊分类"
        verbose_name_plural = "亚马逊分类"
    
class upc_xue(models.Model):
    id = models.AutoField('UPC编号', primary_key=True)
    upc = models.CharField('UPC', max_length=64, null=False)
    created_at = models.DateTimeField('创建时间', auto_now_add=True, blank=True)
    upload_at = models.DateTimeField('使用时间', auto_now_add=True, blank=True)
    class Meta:
        db_table = 'app_upc_xue'

class ShopWhiteList(models.Model):
    id = models.AutoField('白名单编号', primary_key=True)
    # shop_type = models.CharField('店铺类型', max_length=64, null=False)
    shop_type_choice = (
        ('ebay', 'ebay.com店铺'),
        ('ali', 'aliexpress.com店铺'),
    )
    shop_type = models.CharField('店铺类别',
                                 max_length=20,
                                 choices=shop_type_choice)
    shop_name = models.CharField('店铺名称', max_length=64, null=False)
    class Meta:
        db_table = 'shopwhite_list' 
 
 

class FollowPriceXue(models.Model):
    id = models.AutoField('跟踪编号', primary_key=True)
     
    storename = models.CharField('店铺', max_length=64, null=False, default='Amazon')
    username = models.CharField('负责人', max_length=64, null=False)
    keywords = models.CharField('关键词', max_length=800, null=False)
    downloaddetailatonce = models.BooleanField('是否立刻下载详情页',default=False)
    timestamp = models.IntegerField('创建时间戳', blank=True, default=0)
    shop_type_choice = (
        ('amazon', 'amazon.com店铺'),
        ('walmart', 'walmart.com店铺'),
    )
    shop_type = models.CharField('店铺类别',
                                 max_length=20,
                                 choices=shop_type_choice)
    title = models.CharField('标题', max_length=800, null=False)
    title2 = models.CharField('标题2', max_length=800, null=True, blank=True)
    newtitle = models.CharField('标题-ai生成', max_length=800, null=True, blank=True)
    link = models.CharField('链接', max_length=2000, null=False)  # 增加长度以适应亚马逊长链接
    main_image = models.CharField('主图路径', max_length=255, blank=True)
    price = models.CharField('美元价格', max_length=64, null=False)
    currency = models.CharField('网站显示货币', max_length=64, null=True, default='USD')
    salesvolume = models.FloatField('销量', null=True, blank=True)  # 亚马逊可能没有具体销量

    salesvolume2 = models.FloatField('销量2', null=True, blank=True)
    main_image2 = models.CharField('图片2', max_length=800, null=True, blank=True)
    price2 = models.CharField('价格2', max_length=64, null=True, blank=True)
    itemid = models.CharField('ASIN', max_length=255, blank=True, null=True)  # 重命名为ASIN
    productid = models.CharField('平台产品编号',max_length=255, blank=True, null=True)
    lowvalue = models.CharField('跟价最低价格', max_length=64, null=False)
    opdate = models.DateTimeField('操作日期', auto_now=False, auto_now_add=True)
    last_detaildownload_date = models.DateTimeField('最后详情页下载日期', null=True, blank=True)
    followstatus = models.CharField('跟价状态',
                                    max_length=64,
                                    default='未跟价',
                                    null=True)
    memo = models.CharField('备注', max_length=800, null=True, blank=True)
    
    # 新增字段
    rating = models.FloatField('评分', null=True, blank=True)  # 商品评分
    prime = models.BooleanField('Prime商品', default=False)  # 是否为Prime商品
    seller = models.CharField('卖家', max_length=200, null=True, blank=True)  # 卖家信息
    parent_asin = models.CharField('父ASIN', max_length=255, blank=True, null=True)  # 新增
    variation_attributes = models.CharField('变体属性', max_length=200, null=True, blank=True)  # 新增
    bullet_points = models.CharField('产品要点', max_length=200, null=True, blank=True)  # 新增
    technical_details = models.CharField('技术细节', max_length=200, null=True, blank=True)  # 新增
    best_sellers_rank = models.CharField('畅销排名', max_length=200, null=True, blank=True)  # 新增
    is_amazon_choice = models.BooleanField('Amazon推荐', default=False)  # 新增
    is_prime_exclusive = models.BooleanField('Prime专享', default=False)  # 新增
    fulfillment_type = models.CharField('配送类型', max_length=50, null=True, blank=True)  # 新增
    warehouse_location = models.CharField('仓库位置', max_length=200, null=True, blank=True)  # 新增
    aplus_content = models.TextField('A+内容', null=True, blank=True)  # 新增
    buybox_winner = models.CharField('黄金购物车卖家', max_length=200, null=True, blank=True)  # 新增
    offer_count = models.IntegerField('报价数量', null=True, blank=True)  # 新增
    variation_count = models.IntegerField('变体数量', null=True, blank=True)  # 新增
    last_price_change = models.DateTimeField('最后价格变动时间', null=True, blank=True)  # 新增
    is_captcha = models.BooleanField('触发验证', default=False)  # 新增反爬验证状态字段
    capturedatetowalmart=models.DateTimeField('沃尔玛抓取时间', null=True, blank=True)
    # 修改现有字段增强亚马逊适配性
    uploadtowalmartdate=models.DateTimeField('沃尔玛上传时间', null=True, blank=True)
    price = models.DecimalField('美元价格', max_digits=10, decimal_places=2, null=True)  # 改为Decimal类型
    rating = models.DecimalField('评分', max_digits=3, decimal_places=1, null=True, blank=True)  # 改为3.1分格式
    reviews_count = models.IntegerField('评论数', null=True, blank=True)  # 改为3.1分格式
    last_price_change = models.DateTimeField('最后价格变动时间', null=True, blank=True)
    can_pubished=models.BooleanField('是否可上新',default=False )
    sales_info=models.CharField('销量信息', max_length=200, null=True, blank=True)  # 改为结构化存储
    badges=models.CharField('徽章信息', max_length=200, null=True, blank=True)  # 改为结构化存储
    delivery_info=models.CharField('配送信息', max_length=200, null=True, blank=True)  # 改为结构化存储
    brand=models.CharField('品牌信息', max_length=200, null=True, blank=True)  # 改为结构化存储
    category=models.CharField('商品分类', max_length=200, null=True, blank=True)  # 存储多级分类
    seller=models.CharField('卖家信息', max_length=200, null=True, blank=True)  # 改为结构化存储
    in_stock=models.BooleanField('是否在仓库里',default=True )
    mainpic=models.CharField('主图', max_length=200, null=True, blank=True)
    pic=models.CharField('主图', max_length=200, null=True, blank=True)
    image1 = models.CharField('图片1路径', max_length=255, blank=True)
    image2 = models.CharField('图片2路径', max_length=255, blank=True)
    image3 = models.CharField('图片3路径', max_length=255, blank=True)
    image4 = models.CharField('图片4路径', max_length=255, blank=True)
    image5 = models.CharField('图片5路径', max_length=255, blank=True)
    image6 = models.CharField('图片6路径', max_length=255, blank=True)
    image7 = models.CharField('图片7路径', max_length=255, blank=True)


    detail1=models.CharField('5点描述1', max_length=2000, null=True, blank=True)
    detail2=models.CharField('5点描述2', max_length=2000, null=True, blank=True)
    detail3=models.CharField('5点描述3', max_length=2000, null=True, blank=True)
    detail4=models.CharField('5点描述4', max_length=200, null=True, blank=True)
    detail5=models.CharField('5点描述5', max_length=200, null=True, blank=True)

    length=models.FloatField('长度', null=True, blank=True)
    width=models.FloatField('宽度', null=True, blank=True)
    height=models.FloatField('高度', null=True, blank=True)
    lengthunit=models.CharField('长度单位', max_length=200, null=True, blank=True)
    weight=models.FloatField('重量', null=True, blank=True)
    weightunit=models.CharField('重量单位', max_length=200, null=True, blank=True)

    Department=models.CharField('部门', max_length=200, null=True, blank=True)
    publishdate=models.CharField('发布日期', max_length=200, null=True, blank=True)
    Manufacturer=models.CharField('制造商', max_length=200, null=True, blank=True)
    main_rank=models.CharField('主排名', max_length=200, null=True, blank=True)
    maincategory=models.CharField('主分类', max_length=200, null=True, blank=True)
    sub_rank=models.CharField('子排名', max_length=200, null=True, blank=True)
    sun_subcategory=models.CharField('子分类', max_length=200, null=True, blank=True)
    product_dimensions=models.CharField('产品尺寸', max_length=200, null=True, blank=True)
    best_sellers_rank=models.CharField('畅销排名', max_length=200, null=True, blank=True)
    item_model_number=models.CharField('产品型号', max_length=200,  null=True, blank=True)
    @property
    def safe_itemid(self):
        return self.itemid or ''

    class Meta:
        db_table = 'app_product_xue'
        verbose_name = 'amazon链接'
        verbose_name_plural = 'amazon链接'
        permissions = [
            ("can_manage_followprice_admin",
             "Can manage followprice in admin"),
        ]
        indexes = [
            models.Index(fields=['itemid']),
            models.Index(fields=['parent_asin']),
            models.Index(fields=['brand']),
        ]

 
        
class SkuKeywordsXue(models.Model):
    username = models.CharField('创建人', max_length=64, null=True)

    id = models.AutoField('关键词编号', primary_key=True)
    keywords = models.CharField('关键词', max_length=64, null=False, default='')
    shop_type_choice = (
        ('amazon', 'amazon.com店铺'),
        
    )
    shop_type = models.CharField('店铺类别',
                                 max_length=20,
                                 choices=shop_type_choice,default='amazon')
    opdate = models.DateTimeField('更新时间', auto_now=True)
    downloaddetailatonce = models.BooleanField('是否立刻下载详情页',default=False)
    detaildownfrequency = models.IntegerField('详情页下载频率(天)',default=1)
    productcounts=models.IntegerField('产品数量',default=0,blank=True,null=True)
    download_date    = models.DateTimeField('下载日期',default=None, blank=True, null=True)
    def save(self, *args, **kwargs):
        if not self.username and hasattr(self, '_current_user'):
            self.username = self._current_user.username
        super().save(*args, **kwargs)
    class Meta:
        db_table = 'app_keywords_xue'
        verbose_name = "关键词sku名单"
        verbose_name_plural = "输入关键词，可以下载amazon平台产品"




class Rates(models.Model):
    id = models.AutoField('汇率编号', primary_key=True)
    currency_name = models.CharField('货币名称', max_length=64, null=True)
    currency_simplename = models.CharField('货币简称-需要和网站一致',
                                           max_length=64,
                                           null=True)
    rate = models.FloatField('汇率', max_length=64, null=True)
    opdate = models.DateTimeField('更新时间', auto_now=True)

    class Meta:
        db_table = 'rates'
        verbose_name = "货币汇率列"

        verbose_name_plural = '当前主流货币汇率列表'

class PriceHistory(models.Model):
    product = models.ForeignKey(FollowPriceXue, on_delete=models.CASCADE)
    price = models.DecimalField(max_digits=10, decimal_places=2)
    timestamp = models.DateTimeField(auto_now_add=True)
class CrawlExcept(models.Model):
    id = models.AutoField('异常编号', primary_key=True)
    shop_type_choice = (
        ('ebay', 'ebay.com店铺'),
        ('ali', 'aliexpress.com店铺'),
    )
    opdate = models.DateTimeField('操作日期', auto_now=False, auto_now_add=False, null=True)

    shop_type = models.CharField('关键词类别',
                                 max_length=20,
                                 choices=shop_type_choice)
    keywords = models.CharField('关键词', max_length=64, null=True)
    exceptcon = models.CharField('异常信息 ', max_length=2000, null=True)

    class Meta:
        db_table = 'crawlexcept'
        verbose_name = "下载异常"
import django.utils.timezone as timezone
@receiver(pre_save, sender=FollowPriceXue)
def check_price_change(sender, instance, **kwargs):
    if instance.pk:
        original = sender.objects.get(pk=instance.pk)
        if original.price != instance.price:
            instance.last_price_change = timezone.now()

class PriceChange(models.Model):
    productid = models.CharField("产品ID",max_length=255     )
    old_price = models.DecimalField("旧价格",max_digits=10, decimal_places=2, )
    new_price = models.DecimalField("新价格",max_digits=10, decimal_places=2, )
    opdate = models.DateField("操作日期")

    def __str__(self):
        return f"{self.productid} - Old Price: {self.old_price}, New Price: {self.new_price}"

    class Meta:
        verbose_name = "价格变动"
        verbose_name_plural = "价格变动"
        db_table = 'app_pricechange_xue'      
class RankChange(models.Model):
    productid = models.CharField("产品ID",max_length=255, )
    old_main_rank = models.IntegerField("旧主类目排名")
    new_main_rank = models.IntegerField("新主类目排名")
    old_sub_rank = models.IntegerField("旧子类目排名")
    new_sub_rank = models.IntegerField("新子类目排名")
    old_sales_info = models.CharField("旧销售数量",max_length=255, )
    new_sales_info = models.CharField("新销售数量",max_length=255, )
    old_rating = models.FloatField("旧评分")
    new_rating = models.FloatField("新评分")
    old_reviews_count = models.IntegerField("旧评论数")
    new_reviews_count = models.IntegerField("新评论数")
    opdate = models.DateField("操作日期")

    def __str__(self):
        return f"{self.productid} - Old Rank: {self.old_main_rank}, New Rank: {self.new_main_rank}"

    class Meta:
        verbose_name = "排名变动"
        verbose_name_plural = "排名变动"
        db_table = 'app_rankchange_xue'
class CategoryDefault(models.Model):
    label=models.CharField('属性名称',max_length=255,null=True,blank=True)
    inputvalue=models.CharField('默认值',max_length=255,null=True,blank=True)
    input_type_choice = (
        ('select', '下拉框'),
        ('input', '输入框'),

        
    )
    input_type = models.CharField('类型',
                                 max_length=20,
                                 choices=input_type_choice,default='amazon')
    created_at=models.DateTimeField('创建时间',auto_now_add=True)
    updated_at=models.DateTimeField('更新时间',auto_now=True)
    option_index=models.IntegerField('排序-从0开始',default=0)
    class Meta:
        db_table = 'app_categorydefault_xue'
        verbose_name = "类目默认值"
        verbose_name_plural = "类目默认值"
class ProductAnalysis(models.Model):
    """产品分析结果表"""
    productid = models.CharField(max_length=50, db_index=True)
    market_demand = models.FloatField(null=True, blank=True, verbose_name='市场需求指数')
    competition_level = models.FloatField(null=True, blank=True, verbose_name='竞争程度指数')
    profitability = models.FloatField(null=True, blank=True, verbose_name='盈利能力指数')
    growth_potential = models.FloatField(null=True, blank=True, verbose_name='增长潜力指数')
    surge_index = models.FloatField(null=True, blank=True, verbose_name='飙升指数')
    rank_change = models.FloatField(null=True, blank=True, verbose_name='排名变化')
    sales_change = models.FloatField(null=True, blank=True, verbose_name='销量变化')
    review_change = models.FloatField(null=True, blank=True, verbose_name='评论变化')
    is_surging = models.BooleanField(default=False, verbose_name='是否飙升')
    optimized_title = models.CharField(max_length=500, null=True, blank=True, verbose_name='优化后的标题')
    optimized_bullets = models.TextField(null=True, blank=True, verbose_name='优化后的五点描述(JSON格式)')
    keyword_suggestions = models.TextField(null=True, blank=True, verbose_name='关键词建议(JSON格式)')
    confidence_score = models.FloatField(null=True, blank=True, verbose_name='置信度分数')
    analysis_date = models.DateTimeField(default=timezone.now, verbose_name='分析日期')
    created_at = models.DateTimeField(auto_now_add=True, verbose_name='创建时间')
    updated_at = models.DateTimeField(auto_now=True, verbose_name='更新时间')

    class Meta:
        verbose_name = '产品分析'
        verbose_name_plural = '产品分析'
        ordering = ['-analysis_date']
        db_table = 'app_product_analysis'

    def __str__(self):
        return f'产品分析 - {self.productid}'

    @property
    def product(self):
        """关联产品"""
        from .models import ProductXue
        return ProductXue.objects.filter(productid=self.productid).first()

    @classmethod
    def get_latest_analysis(cls, productid):
        """获取产品最新的分析结果"""
        return cls.objects.filter(productid=productid).order_by('-analysis_date').first()

    @classmethod
    def get_surging_products(cls, limit=10):
        """获取飙升产品列表"""
        return cls.objects.filter(is_surging=True).order_by('-surge_index')[:limit]

    @classmethod
    def get_market_metrics(cls):
        """获取市场指标"""
        from django.db.models import Avg
        metrics = cls.objects.aggregate(
            avg_market_demand=Avg('market_demand'),
            avg_competition_level=Avg('competition_level'),
            avg_profitability=Avg('profitability')
        )
        
        return {
            'market_demand': metrics['avg_market_demand'] or 0,
            'competition_level': metrics['avg_competition_level'] or 0,
            'profitability': metrics['avg_profitability'] or 0
        }
     